﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;

namespace QualityRpt.Dao.Db2
{
    class NetWorkPlazaDao:INetWorkPlazaDao
    {
        const string cachePrefix = "NPL-";
        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.NETWORK_ROADSECTION_PLAZA(ORGAN_ID,ORGAN_CHARACTER,ORGAN_NO,ORGAN_NAME,ORGAN_ALIAS,ADMINSUPER_ID,STATE) VALUES(?,?,?,?,?,?,?)",
                    ODBCHelper.CreateInputParameter("ORGAN_ID", OdbcType.Int, Convert.ToInt32(row["ORGAN_ID"])),
                    ODBCHelper.CreateInputParameter("ORGAN_CHARACTER", OdbcType.Int, row["ORGAN_CHARACTER"]),
                    ODBCHelper.CreateInputParameter("ORGAN_NO", OdbcType.Int, row["ORGAN_NO"]),
                    ODBCHelper.CreateInputParameter("ORGAN_NAME", OdbcType.VarChar,512, row["ORGAN_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("ORGAN_ALIAS", OdbcType.VarChar,512, row["ORGAN_ALIAS"].ToString()),
                    ODBCHelper.CreateInputParameter("ADMINSUPER_ID", OdbcType.Int, row["ADMINSUPER_ID"]),
                    ODBCHelper.CreateInputParameter("STATE", OdbcType.Int, row["STATE"]));
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.NETWORK_ROADSECTION_PLAZA SET ORGAN_ID=?,ORGAN_CHARACTER=?,ORGAN_NO=?,ORGAN_NAME=?,ORGAN_ALIAS=?,ADMINSUPER_ID=?,STATE=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ORGAN_ID", OdbcType.Int, Convert.ToInt32(row["ORGAN_ID"])),
                    ODBCHelper.CreateInputParameter("ORGAN_CHARACTER", OdbcType.Int, row["ORGAN_CHARACTER"]),
                    ODBCHelper.CreateInputParameter("ORGAN_NO", OdbcType.Int, row["ORGAN_NO"]),
                    ODBCHelper.CreateInputParameter("ORGAN_NAME", OdbcType.VarChar, 512, row["ORGAN_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("ORGAN_ALIAS", OdbcType.VarChar, 512, row["ORGAN_ALIAS"].ToString()),
                    ODBCHelper.CreateInputParameter("ADMINSUPER_ID", OdbcType.Int, row["ADMINSUPER_ID"]),
                    ODBCHelper.CreateInputParameter("STATE", OdbcType.Int, row["STATE"]),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, Convert.ToInt32(row["ID"])));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            DataTable rtTable = GetList("ID=" + row["ID"].ToString());
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public DataRow Get(long id)
        {
            DataTable rtTable = GetList(" AND ID="+id);
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.NETWORK_ROADSECTION_PLAZA WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }

        public DataTable GetList(string where)
        {
            return GetList(where, "ID ASC");
        }

        public DataTable GetList(string where, string sort)
        {
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.NETWORK_ROADSECTION_PLAZA ");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            DataTable rtTable = null;
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        public DataRow GetByORGAN_ID(int ORGAN_ID)
        {
            DataRow rtRow = null;
            DataTable rtTable = GetList("ORGAN_ID=" + ORGAN_ID);
            if (rtTable.Rows.Count > 0)
            {
                rtRow = rtTable.Rows[0];
            }
            return rtRow;
        }
        public DataTable GetPageList(Utils.PageUtils pu, string where, string sort)
        {
            throw new NotImplementedException();
        }

        public DataTable GetRoadList()
        {
            return GetList(" ORGAN_CHARACTER = 2");//级别为2的为路段
        }
        public DataTable GetListByRdIn(string rdin)
        {
            StringBuilder sbtext = new StringBuilder();
            sbtext.Append(" ORGAN_CHARACTER = 3");
            if (rdin!=null && rdin.Trim() != string.Empty)
            {
                sbtext.Append(" AND ADMINSUPER_ID IN( " + rdin + ")");
            }
            return GetList(sbtext.ToString());
        }
        public DataRow GetRoadByName(string name)
        {
            DataRow rtRow = null;
            DataTable table = GetList("ORGAN_NAME = '" + name + "' AND ORGAN_CHARACTER = 2");
            if (table.Rows.Count > 0)
            {
                rtRow = table.Rows[0];
            }
            return rtRow;
        }
        public DataRow GetPlazaByName(string name,int rdid)
        {
            DataRow rtRow = null;
            DataTable table = GetList("ORGAN_NAME = '" + name + "' AND ORGAN_CHARACTER = 3 AND ADMINSUPER_ID = " + rdid);
            if (table.Rows.Count > 0)
            {
                rtRow = table.Rows[0];
            }
            return rtRow;
        }
        public DataRow SaveAJoinRow(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.AJOIN_LANE(MAIN_PLAZA_ID,MAIN_PLAZA_NAME,AJOIN_PLAZA_ID,AJOIN_PLAZA_NAME) VALUES(?,?,?,?)",
                    ODBCHelper.CreateInputParameter("MAIN_PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["MAIN_PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("MAIN_PLAZA_NAME", OdbcType.VarChar,50, row["MAIN_PLAZA_NAME"]),
                    ODBCHelper.CreateInputParameter("AJOIN_PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["AJOIN_PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("AJOIN_PLAZA_NAME", OdbcType.VarChar, 50, row["AJOIN_PLAZA_NAME"]));
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow UpdateAJoinRow(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.AJOIN_LANE SET MAIN_PLAZA_ID=?,MAIN_PLAZA_NAME=?,AJOIN_PLAZA_ID=?,AJOIN_PLAZA_NAME=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("MAIN_PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["MAIN_PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("MAIN_PLAZA_NAME", OdbcType.VarChar, 50, row["MAIN_PLAZA_NAME"]),
                    ODBCHelper.CreateInputParameter("AJOIN_PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["AJOIN_PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("AJOIN_PLAZA_NAME", OdbcType.VarChar, 50, row["AJOIN_PLAZA_NAME"]),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, Convert.ToInt32(row["ID"])));
            }
            return row;
        }

        public DataTable GetAJoinSchema()
        {
            return GetAJoinList("ID=0", string.Empty).Clone();
        }

        public DataTable GetAJoinList(string where, string sort)
        {
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.AJOIN_LANE ");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                return ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
            }
        }

        public DataTable GetByMainPlazaID(int id)
        {
            return GetAJoinList("MAIN_PLAZA_ID=" + id, string.Empty);
        }

        public DataTable GetSchema()
        {
            return GetList("ID=0").Clone();
        }
        public DataRow GetAJoin(int id)
        {
            DataRow rtRow = null;
            DataTable rtTable = GetAJoinList("ID="+id,string.Empty);
            if (rtTable.Rows.Count > 0)
            {
                rtRow = rtTable.Rows[0];
            }
            return rtRow;
        }


        public void DeleteAJoin(int id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.AJOIN_LANE WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
        }

        public void DeleteAJoinByMP(int mpid)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.AJOIN_LANE WHERE MAIN_PLAZA_ID=?",
                    ODBCHelper.CreateInputParameter("MAIN_PLAZA_ID", OdbcType.Int, mpid));
            }
        }
        
    }
}
